package com.jbm;

import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

/**
 * Created by root on 18-12-11.
 */
public class JBM0001 implements DoProcess
{
	public String Proc(String json)
	{
		int code = 0;
		String info = "";

		JsonElement content = null;

		Connection conn = null;
		Statement stmt = null;

		try
		{
			JsonParser parser = new JsonParser();
			JsonObject req = (JsonObject) parser.parse(json);

			JsonElement jmethod = req.get("method");

			if ( jmethod == null )
				throw new Exception("方法名不能为空");

			String method = jmethod.toString();

			JsonElement jid = req.get("id");

			String id = null;

			if ( jid != null )
				id = jid.getAsString();

			JsonElement jmobile = req.get("mobile");

			String mobile = null;

			if ( jmobile != null )
				mobile = jmobile.getAsString();

			if ( GDHandler.db_pool != null )
			{
				conn = (Connection) GDHandler.db_pool.take();

//			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/wycore?"
//					+ "user=root&password=password&useUnicode=true&characterEncoding=UTF8");
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			}else
				throw new Exception("DB error");

			switch (method)
			{
				case "\"create_order\"":
				{
					JsonElement jcontract = req.get("wy_contract");
					JsonElement jwycode = req.get("wy_code");
					JsonElement jamount = req.get("amount");
					JsonElement jopenid = req.get("openid");
					JsonElement jsertype = req.get("ser_type");

					if ( jopenid == null || jopenid.isJsonNull() ||
							jwycode == null || jwycode.isJsonNull() ||
							jamount == null || jamount.isJsonNull()
							)
					{
						code = 400;
						info = "param error";

						break;
					}

					String sertype = "wy";

					if ( jsertype != null && !jsertype.isJsonNull() )
						sertype = jsertype.getAsString();

					SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmssSSS");

					code = 200;
					info = "OK";

					JsonObject jcontent = new JsonObject();

					String orderid = format.format(new java.util.Date());

					jcontent.addProperty("order_id", orderid);

					int user_id = 0;

					String contract = "";

					if ( jcontract != null && !jcontract.isJsonNull() && !jcontract.getAsString().equals(""))
					{
						contract = jcontract.getAsString();

						String sql = "select user_id from user where right(contract_no,10)='" + jcontract.getAsString() + "'";

						ResultSet rs = stmt.executeQuery(sql);

						rs.last();
						int rcnt = rs.getRow();
						rs.first();

						if (rcnt != 1 && sertype.equals("wy"))
						{
							code = 404;

							if (rcnt == 0)
								info = "未知的合同号:" + jcontract.getAsString();
							else
								info = "重复的合同号:" + jcontract.getAsString();

							rs.close();

							break;
						}else if ( rcnt == 1 )
							user_id = rs.getInt(1);

						rs.close();
					}

					String sql = "insert into payorder ( porder_code, wy_code, contract, amount, user_id, unpay_open_id, ser_type ) values " +
							"( '"+orderid+"', '"+jwycode.getAsString()+"', '"+contract+"', '"+jamount.getAsString()+"', "+user_id+", '"+jopenid.getAsString()+"', '"+sertype+"' )";
					stmt.executeUpdate(sql);

					content = jcontent;

					break;
				}
				case "\"get_pay_list\"":
				{
					JsonElement jopenid = req.get("openid");

					if ( jopenid == null || jopenid.isJsonNull())
					{
						code = 400;
						info = "param error";
					}

					code = 200;
					info = "OK";

					String openid = jopenid.getAsString();

					openid = openid.replaceAll(" ","+");

					String sql = "select contract, amount, status, create_date, pay_date, ser_type from payorder where unpay_open_id = '"+openid+"'";

					ResultSet rs = stmt.executeQuery(sql);
					rs.last();
					int rcnt = rs.getRow();
					rs.first();

					JsonArray plist = new JsonArray();

					if ( rcnt > 0 )
					{
						do
						{
							JsonObject pay = new JsonObject();
							String contract,amount,status,create_date,pay_date,sertype;

							pay.addProperty("contract", contract = rs.getString("contract"));
							pay.addProperty("amount", amount = rs.getString("amount"));
							pay.addProperty("status", status = rs.getString("status"));
							pay.addProperty("create_date", create_date = rs.getString("create_date"));
							pay.addProperty("pay_date", pay_date = rs.getString("pay_date"));
							pay.addProperty("ser_type", sertype = rs.getString("ser_type"));

							String text = null;

							switch (sertype)
							{
								case "wy":
									text = "合同号:"+contract+" 物业费:";
									break;
								case "clean":
									text = "合同号:"+contract+" 家政清洁费:";
									break;
								default:
									text = "合同号:"+contract+" 其他费:";
									break;
							}

							text += amount +" ";
							switch (status)
							{
								case "unpay":
									text += "未付款 ";
									break;
								case "paied":
									text += "已付款 ";
									break;
								case "adddate":
									text += "已展期 ";
									break;
								default:
									text += "未知 ";
									break;
							}

							if ( pay_date != null && !"".equals(pay_date))
								text += "支付时间"+pay_date;

							pay.addProperty("text", text);

							plist.add(pay);
						} while (rs.next());
					}

					rs.close();

					code = 200;
					info = "OK";
					content = plist;

					break;
				}
				case "\"get_contract_info\"":
				{
					JsonElement jcontract = req.get("wy_contract");

					if ( jcontract == null || jcontract.isJsonNull())
					{
						code = 400;
						info = "param error";
					}

					code = 200;
					info = "OK";

					String contract = jcontract.getAsString();

					String sql = "select real_name, door_no, tel, manage_fee, manage_fee_date, " +
							"case when manage_fee_date > now() then '否' else '是' end is_exp from user where right(contract_no,10) = '"+jcontract.getAsString()+"'";

					ResultSet rs = stmt.executeQuery(sql);
					rs.last();
					int rcnt = rs.getRow();
					rs.first();

					JsonObject jrs = new JsonObject();

					if ( rcnt > 0 )
					{
						jrs.addProperty("real_name",  rs.getString("real_name"));
						jrs.addProperty("door_no", "天津市河北区盛宁家园"+rs.getString("door_no").replace("-","号楼"));
						jrs.addProperty("tel", rs.getString("tel"));
						jrs.addProperty("manage_fee", rs.getString("manage_fee")+"/年");
						jrs.addProperty("manage_fee_date", rs.getString("manage_fee_date"));
						jrs.addProperty("is_exp", rs.getString("is_exp"));

						code = 200;
						info = "OK";
						content = jrs;
					}else
					{
						code = 404;
						info = "未知的合同号:"+jcontract.getAsString();
					}

					rs.close();

					break;
				}
				case "\"confirm_pay\"":
				{
					JsonElement jorderid = req.get("order_id");
					JsonElement jserial = req.get("unpay_serial");

					if ( jorderid == null || jorderid.isJsonNull() ||
							jserial == null || jserial.isJsonNull())
					{
						code = 400;
						info = "param error";

						break;
					}

					code = 200;
					info = "OK";

					break;
				}
				default:
					code = 404;
					info = "不支持的方法名:"+method;
					break;
			}
		}catch (Exception e)
		{
			code = 500;
			info = e.getMessage();

			if ( info == null )
				info = "Failed";

			content = null;
		}

		try
		{
			if (stmt != null)
				stmt.close();

			if ( conn != null )
				GDHandler.db_pool.put(conn);
		}catch (Exception e)
		{
		}

		JsonObject response = new JsonObject();

		response.addProperty("code",code);
		response.addProperty("errorinfo",info);
		if ( content != null )
			response.add("content",content);

		return response.toString();
	}

	public void Begin()
	{
		if ( GDHandler.db_pool != null )
		{
		}
	}

	public String UNPay( String json )
	{
		SysLog.g_syslog.MakeLog(SysLog.SYS_LOG_LEVEL_DEBUG,"UNPay");
		JsonParser parser = new JsonParser();
		JsonObject req = (JsonObject) parser.parse(json);

		Connection conn = null;
		Statement stmt = null;
		int code = 500;
		String info = "FAILED";

		try
		{
			if ( GDHandler.db_pool != null )
			{
				conn = (Connection) GDHandler.db_pool.take();

//			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/wycore?"
//					+ "user=root&password=password&useUnicode=true&characterEncoding=UTF8");
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			}else
				throw new Exception("DB error");

			conn.setAutoCommit(false);

			String sql = "update payorder set status = 'paied', pay_date = now(), unserial = '"+req.get("queryId").getAsString()+"' " +
				"where status = 'unpay' and porder_code = '"+req.get("orderId").getAsString()+"'";
			int update = stmt.executeUpdate(sql);

			if ( update == 0 )
				throw new Exception("update payorder to paied error");

			sql = "select amount,user_id,ser_type from payorder where porder_code = '"+req.get("orderId").getAsString()+"'";
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			BigDecimal amount = rs.getBigDecimal("amount");
			int user_id = rs.getInt("user_id");
			String sertype = rs.getString("ser_type");
			rs.close();

			if ( user_id != 0 && sertype.equals("wy") )
			{
				sql = "insert into payment_log( contract_no, amount, pay_date, pay_type, pay_type_name, pay_method, user_id) " +
						"select contract, amount, pay_date, 'manage', '物业费', 1, user_id from payorder where porder_code = '" + req.get("orderId").getAsString() + "'";
				stmt.executeUpdate(sql);

				sql = "select egcard, manage_fee, manage_fee_date, manage_fee_rate, block_data from user where user_id = " + user_id;
				rs = stmt.executeQuery(sql);
				rs.next();
				BigDecimal manage_fee = rs.getBigDecimal("manage_fee");
				Date manage_fee_date = rs.getDate("manage_fee_date");
				int manage_fee_rate = rs.getInt("manage_fee_rate");
				String block_data = rs.getString("block_data");
				int egcard = rs.getInt("egcard");
				rs.close();

				int ms = manage_fee_rate == 1 ? 1 :
						manage_fee_rate == 2 ? 3 :
								manage_fee_rate == 3 ? 12 : 0;

				if (ms != 0)
				{
					amount = amount.multiply(new BigDecimal(ms));
					amount = amount.divide(manage_fee, 2);

					int addms = amount.intValue();

					Calendar rightNow = Calendar.getInstance();
					rightNow.setTime(manage_fee_date);
					rightNow.add(Calendar.MONTH, addms);

					int year = rightNow.get(Calendar.YEAR);
					int month = rightNow.get(Calendar.MONTH);
					int day = rightNow.get(Calendar.DAY_OF_MONTH);

					SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

					String mfdate = format.format(rightNow.getTime());

					String hdate = String.format("%04x%02x%02x", year, month + 1, day);
					hdate = hdate.toUpperCase();
					hdate = hdate.substring(2, 4) + hdate.substring(0, 2) + hdate.substring(4);

					block_data = block_data.substring(0, 24) + hdate;

					sql = "update user set manage_fee_date = '" + mfdate + "', block_data = '" + block_data + "' where user_id = " + user_id;
					update = stmt.executeUpdate(sql);

					if (update == 0)
						throw new Exception("update user error");

					sql = "update payorder set status = 'adddate' " +
							"where status = 'paied' and porder_code = '" + req.get("orderId").getAsString() + "'";
					update = stmt.executeUpdate(sql);

					if (update == 0)
						throw new Exception("update payorder to adddate error");
				}
				sql = "select distinct reg.equard_code, reg.open_key from entrance_guard eg " +
						"join eguard_key akey on akey.eguard_id = eg.eguard_id " +
						"join eguard_key ekey on ekey.source_id = akey.egkey_id " +
						"join m_gi mgi on mgi.gi_id = eg.gi_id " +
						"join entrance_guard aeg on aeg.gi_id = mgi.prev_id " +
						"join entrance_guard reg on reg.eguard_id = aeg.prev_id " +
						"join user u on ekey.id_card = u.idnumber " +
						"where u.user_id = "+user_id+" and aeg.prev_id > 0 ";
				rs = stmt.executeQuery(sql);
				rs.last();
				int rcnt = rs.getRow();
				rs.first();

				if ( rcnt > 0 )
				{
					do
					{
						String ip = rs.getString("equard_code");
						int gid = rs.getInt("open_key");

						ConnectAgent ca = null;

						ca = (ConnectAgent)GDHandler.gd_pool.Get(null,null,ip);
						if ( ca != null )
							ca.Send(Guard.getBlackCMD("remove",gid,egcard));
					} while (rs.next());
				}

				rs.close();
			}

			conn.commit();

			code = 200;
			info = "OK";
		}catch (Exception e)
		{
			SysLog.g_syslog.MakeLog(SysLog.SYS_LOG_LEVEL_DEBUG,"Error:%s",e.getMessage());
			try
			{
				if ( conn != null && !conn.getAutoCommit())
					conn.rollback();
			}catch (Exception ee)
			{}

			code = 500;
			info = e.getMessage();
		}

		try
		{
			if (stmt != null)
				stmt.close();

			if ( conn != null )
			{
				conn.setAutoCommit(true);
				GDHandler.db_pool.put(conn);
			}
		}catch (Exception e)
		{
		}

		JsonObject response = new JsonObject();

		response.addProperty("code",code);
		response.addProperty("errorinfo",info);

		return response.toString();
	}
}
